package com.css.fxfzdzzh.modules.reportManagement.repository;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.FxfzConstants;
import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.reportManagement.entity.ReportCheck;
import com.css.fxfzdzzh.modules.reportManagement.entity.ReportCheckRecode;
import com.css.fxfzdzzh.modules.reportManagement.param.ReportCheckQueryParam;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntity;
import com.css.fxfzdzzh.util.CreateGson;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import com.css.fxfzdzzh.zcpt.sys.entity.SUser;
import com.css.fxfzdzzh.zcpt.sys.service.SUserService;
import com.css.fxfzdzzh.zcpt.sys.service.SysAreaService;
import com.css.fxfzdzzh.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年05月12日  11:08
 */
@Repository
public class ReportCheckRepository {

    @Resource
    private SUserService sUserService;

    @Resource
    SysRoleService  sysRoleService;
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    SysAreaService sysAreaService;
    public Map<String, Object> page(ReportCheckQueryParam param, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from (select dtmr_id as id,num,task_name,submit_user_name, upload_multiple_report_name as report_name,disk_upload_multiple_report_name localReport,report_save_path reportSavePath ,district,'2' as report_type,check_status,audit_time from dzzh_task_multiple_report ");
        StringBuilder whereSql = new StringBuilder(" where T.check_status='1'");
        StringBuilder countSql = new StringBuilder(" select count(*) from (select dtmr_id as id,num,task_name,submit_user_name, upload_multiple_report_name as report_name,disk_upload_multiple_report_name localReport,report_save_path reportSavePath,district,'2' as report_type,check_status,audit_time from dzzh_task_multiple_report  ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE)) {
            //省管理员可查看和审核本省报告滑坡/砂土液化/软土震陷评估报告
            String areaName = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            sql.append(") t");
            countSql.append(") t");
            whereSql.append("  and t.district like '%").append(areaName).append("%'");
        }else if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains("dzzh_test")) {
            // 可以查看审核全部（ 地表破裂带评估报告、滑坡/砂土液化/软土震陷评估报告）
            sql.append(" union select dtbr_id as id,num,upload_break_report_name as task_name,submit_user_name, upload_break_report_name as report_name,disk_upload_break_report_name localReport,report_save_path reportSavePath,district,'1' as report_type,check_status,audit_time from dzzh_task_break_report ) t");
            countSql.append(" union select dtbr_id as id,num,upload_break_report_name as task_name,submit_user_name, upload_break_report_name as report_name,disk_upload_break_report_name localReport,report_save_path reportSavePath,district,'2' as report_type,check_status,audit_time from dzzh_task_break_report ) t");
        }else{
            sql.append(") t");
            countSql.append(") t");
        }

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(param.getNum())) {
            whereSql.append(" and t.num like '%").append(param.getNum()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getTaskName())) {
            whereSql.append(" and t.task_name like '%").append(param.getTaskName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getSubmitUserName())) {
            whereSql.append(" and t.submit_user_name like '%").append(param.getSubmitUserName()).append("%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
                    if(param.getCity().equals(param.getProvince())){
                        whereSql.append(" and (t.district = '").append(param.getProvince()).append("-").append(param.getArea()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district like '%").append(param.getProvince()).append("-").append("%'  or t.district = '").append(param.getProvince()).append("' or " +
                                " t.district = '").append(param.getProvince()).append(param.getArea()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district like '%").append(param.getProvince()).append("%'  or t.district = '").append(param.getProvince()).append("')");
                    }else{
                        whereSql.append(" and (t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("-").append(param.getArea()).append("'").append(" or t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or t.district like '%,").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("%' or t.district = '").append(param.getProvince()).append("'")
                                .append(" or t.district = '").append(param.getProvince()).append(param.getCity()).append(param.getArea()).append("'").append(" or t.district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or t.district like '%,").append(param.getProvince()).append(param.getCity()).append("' or t.district = '").append(param.getProvince()).append(param.getCity()).append("' or t.district like '%,").append(param.getProvince()).append("%' or t.district like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' )");
                    }
                } else {
                    if(param.getCity().equals(param.getProvince())){
                        //省市相同例如北京、上海、天津和重庆
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append("%' or t.district  like '%,").append(param.getProvince()).append("' or t.district  like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' t.district like '%").append(param.getProvince()).append("%')");
                    }else{
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("%' or t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district = '").append(param.getProvince()).append("'")
                                .append(" or t.district like '%").append(param.getProvince()).append(param.getCity()).append("%' or t.district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or t.district = '").append(param.getProvince()).append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district = '").append(param.getProvince()).append("')");
                    }
                }
            } else {
                whereSql.append(" and (t.district like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' or t.district LIKE '%,").append(param.getProvince()).append("' or t.district LIKE '%").append(param.getProvince()).append("%')");
            }

        }
        String sqlWhole = sql.append(whereSql).append(" order by  T.audit_time desc ").append(limitSql).toString();
        countSql.append(whereSql);
        List<ReportCheck> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(ReportCheck.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    public void saveCheckRecord(ReportCheckRecode reportCheckRecode) {
        String sql =
                "insert into dzzh_report_check_record(drcr_id,"
                        + "num,"
                        + "name,"
                        + "check_status,"
                        + "submit_user_name,"
                        + "submit_user_id,"
                        + "district,"
                        + "check_user_id,"
                        + "check_time,reject_advice,reject_real_file,local_report_name,reject_file,report_name,save_path) values(:drcrId, :num, :name, :checkStatus, :submitUserName, :submitUserId, :district , :checkUserId,"
                        + ":checkTime,:rejectAdvice,:rejectRealFile,:localReportName,:rejectFile,:reportName,:savePath) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(reportCheckRecode);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    public Map<String, Object> recordPage(ReportCheckQueryParam param, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from  dzzh_report_check_record t ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        StringBuilder countSql = new StringBuilder(" select count(*) from  dzzh_report_check_record t ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.DZZH_PRI_GL_ROLE)) {
            //省管理员可查看和审核本省报告滑坡/砂土液化/软土震陷评估报告
            String areaName = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            whereSql.append("  and t.district like '%").append(areaName).append("%'");
        }else if (roles.contains(FxfzConstants.DZZH_COUN_GL_ROLE) || roles.contains("dzzh_test")) {
            // 可以查看全部审核记录
        }
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(param.getNum())) {
            whereSql.append(" and t.num like '%").append(param.getNum()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getTaskName())) {
            whereSql.append(" and t.name like '%").append(param.getTaskName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getSubmitUserName())) {
            whereSql.append(" and t.submit_user_name like '%").append(param.getSubmitUserName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCheckStatus())) {
            whereSql.append(" and t.check_status = '").append(param.getCheckStatus()).append("'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
                    if(param.getCity().equals(param.getProvince())){
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getArea()).append("%' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("'  or t.district = '").append(param.getProvince()).append("' or " +
                                " t.district = '").append(param.getProvince()).append(param.getArea()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district = '").append(param.getProvince()).append("')");
                    }else{
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("-").append(param.getArea()).append("%'").append(" or t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or t.district like '%,").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("%' or t.district = '").append(param.getProvince()).append("'")
                                .append(" or t.district = '").append(param.getProvince()).append(param.getCity()).append(param.getArea()).append("'").append(" or t.district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or t.district like '%,").append(param.getProvince()).append(param.getCity()).append("' or t.district = '").append(param.getProvince()).append(param.getCity()).append("' or t.district like '%,").append(param.getProvince()).append("%' or t.district like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' )");
                    }
                } else {
                    if(param.getCity().equals(param.getProvince())){
                        //省市相同例如北京、上海、天津和重庆
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append("%' or t.district  like '%,").append(param.getProvince()).append("' or t.district  like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' or t.district like '%").append(param.getProvince()).append("%')");
                    }else{
                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("%' or t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append(",%' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district = '").append(param.getProvince()).append("'")
                                .append(" or t.district like '%").append(param.getProvince()).append(param.getCity()).append("%' or t.district like '%").append(param.getProvince()).append(param.getCity()).append(",%' or t.district = '").append(param.getProvince()).append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append(",%' or t.district like '%,").append(param.getProvince()).append("' or t.district = '").append(param.getProvince()).append("')");
                    }
                }
            } else {
                whereSql.append(" and (t.district like '%").append(param.getProvince()).append(",%' or t.district = '").append(param.getProvince()).append("' or t.district LIKE '%,").append(param.getProvince()).append("' or t.district LIKE '%").append(param.getProvince()).append("%')");
            }

        }
//        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
//            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
//                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
//                    if(param.getCity().equals(param.getProvince())){
//                        whereSql.append(" and (t.district = '").append(param.getProvince()).append("-").append(param.getArea()).append("' or t.district like '%").append(param.getProvince()).append("、%' or t.district like '%").append(param.getProvince()).append("-").append("%'  or t.district = '").append(param.getProvince()).append("')");
//                    }else{
//                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("-").append(param.getArea()).append("%'").append(" or t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("、%' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append("、%'or t.district = '").append(param.getProvince()).append("')");
//                    }
//                } else {
//                    if(param.getCity().equals(param.getProvince())){
//                        //省市相同例如北京、上海、天津和重庆
//                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append("%' or t.district  like '%").append(param.getProvince()).append("、%' or t.district = '").append(param.getProvince()).append("')");
//                    }else{
//                        whereSql.append(" and (t.district like '%").append(param.getProvince()).append("-").append(param.getCity()).append("、%' or t.district = '").append(param.getProvince()).append("-").append(param.getCity()).append("' or t.district like '%").append(param.getProvince()).append("、%'or t.district = '").append(param.getProvince()).append("')");
//                    }
//                }
//            } else {
//                whereSql.append(" and (t.district like '%").append(param.getProvince()).append("、%' or t.district = '").append(param.getProvince()).append("' or t.district LIKE '%").append(param.getProvince()).append("-%')");
//            }
//
//        }
        String sqlWhole = sql.append(whereSql).append(" order by t.check_time desc ").append(limitSql).toString();
        countSql.append(whereSql);
        List<ReportCheckRecode> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(ReportCheckRecode.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    public void cancelAudit(String id, String reportType) {
        StringBuilder builder = new StringBuilder("update ");
        if("1".equals(reportType)){
            builder.append("dzzh_task_break_report set reject_file='',reject_real_file='' where dtbr_id='").append(id).append("' and (reject_advice is null or reject_advice='')" );
        }else if("2".equals(reportType)){
            builder.append("dzzh_task_multiple_report set reject_file='',reject_real_file='' where dtmr_id='").append(id).append("' and (reject_advice is null or reject_advice='')" );
        }
        jdbcTemplate.update(builder.toString());
    }
}
